﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.Common;
using System.Configuration;
using System.Data.SqlClient;
using LotteryVote.Entity;
using System.Reflection;

namespace LotteryVote.Data
{
    //TODO:还欠缺SQL执行异常错误日志记录
    public class DataBase : IDisposable
    {
        protected readonly string _connectionString;
        private DbProviderFactory _factory;
        private DbConnection _connection;
        private DbTransaction _transaction;
        private DataTable _dataTable;
        /// <summary>
        /// 是否串联事务
        /// </summary>
        private bool _isTademTransaction = false;

        #region Properties
        /// <summary>
        /// 数据库连接
        /// </summary>
        /// <value>
        /// The connection.
        /// </value>
        public DbConnection Connection
        {
            get { return _connection; }
            set { _connection = value; }
        }
        /// <summary>
        /// 事务
        /// </summary>
        /// <value>
        /// The transaction.
        /// </value>
        public DbTransaction Transaction
        {
            get { return _transaction; }
            set
            {
                _transaction = value;
                _isTademTransaction = true;
            }
        }
        /// <summary>
        /// 上一次执行结果的DataTable.
        /// </summary>
        public DataTable DataTable
        {
            get { return _dataTable; }
            private set { _dataTable = value; }
        }
        #endregion

        public DataBase() : this(DBManager.ConnectionString, DBManager.DBProviderName) { }
        public DataBase(string connectionString, string dbProviderName)
        {
            this._connectionString = connectionString;
            this._factory = DbProviderFactories.GetFactory(dbProviderName);
        }

        /// <summary>
        /// 打开数据库连接.
        /// </summary>
        public void OpenConnection()
        {
            if (this._connection == null)
            {
                this._connection = _factory.CreateConnection();
                this._connection.ConnectionString = this._connectionString;
            }
            if(string.IsNullOrEmpty(this._connection.ConnectionString))
                this._connection.ConnectionString = this._connectionString;
            try
            {
                if (_connection.State != ConnectionState.Open)
                    _connection.Open();
            }
            catch (Exception ex)
            {
                LotteryVote.Utility.LogConsole.Info("_connectionString:" + _connectionString);
                LotteryVote.Utility.LogConsole.Info("DBManager.ConnectionString:" + DBManager.ConnectionString);
                LotteryVote.Utility.LogConsole.Error(_connection.ConnectionString, ex);
            }
        }

        /// <summary>
        ///开始事务
        /// </summary>
        public void BeginTransaction()
        {
            OpenConnection();
            if (this._transaction == null)
                this._transaction = _connection.BeginTransaction();
        }
        /// <summary>
        /// 提交事务并释放关闭资源.
        /// </summary>
        public void CommitTransaction()
        {
            if (_transaction != null && !_isTademTransaction)       //若是串联事务则跳过
            {
                _transaction.Commit();
                _transaction.Dispose();
                _transaction = null;
                _isTademTransaction = false;
            }
        }
        /// <summary>
        /// 回滚并释放关闭资源.
        /// </summary>
        public void RollbackTransaction()
        {
            if (_transaction != null && !_isTademTransaction)
            {
                _transaction.Rollback();
                _transaction.Dispose();
                _transaction = null;
                Dispose();
                _isTademTransaction = false;
            }
        }

        #region Methods
        /// <summary>
        /// 串联两个数据实体（串联连接，事务）
        /// </summary>
        /// <param name="data">The data.</param>
        public void Tandem(DataBase data)
        {
            this.Connection = data.Connection;
            this.Transaction = data.Transaction;
        }
        /// <summary>
        /// 执行SQL返回一个数据集.
        /// </summary>
        /// <param name="sql">SQL语句.</param>
        /// <param name="cmdParams">SQL参数.</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string sql, params DbParameter[] cmdParams)
        {
            return ExecuteDataSet(CommandType.Text, sql, cmdParams);
        }
        /// <summary>
        /// 执行命令返回一个数据集.
        /// </summary>
        /// <param name="cmdType">命令类型.</param>
        /// <param name="cmdText">命令.</param>
        /// <param name="cmdParams">SQL参数.</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        {
            try
            {
                OpenConnection();
                DbCommand cmd = CreateCommand(cmdType, cmdText, cmdParams);
                if (this != null)
                    cmd.Transaction = this.Transaction;
                DbDataAdapter da = _factory.CreateDataAdapter();
                da.SelectCommand = cmd;
                DataSet ds = new DataSet();
                da.Fill(ds);
                this.DataTable = ds.Tables[0];
                return ds;
            }
            finally
            {
                Dispose();
            }
        }
        /// <summary>
        /// 执行SQL语句返回受影响的行数.
        /// </summary>
        /// <param name="sql">SQL语句.</param>
        /// <param name="cmdParams">SQL参数.</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, params DbParameter[] cmdParams)
        {
            return ExecuteNonQuery(CommandType.Text, sql, cmdParams);
        }
        /// <summary>
        /// 执行命令返回受影响的行数
        /// </summary>
        /// <param name="cmdType">命令类型.</param>
        /// <param name="cmdText">命令.</param>
        /// <param name="cmdParams">SQL参数.</param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        {
            try
            {
                OpenConnection();
                DbCommand cmd = CreateCommand(cmdType, cmdText, cmdParams);
                if (this.Transaction != null)
                    cmd.Transaction = this.Transaction;
                this.DataTable = null;
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                Dispose();
            }
        }
        /// <summary>
        /// 执行SQL返回结果集中第一行第一列
        /// </summary>
        /// <param name="sql">SQL语句.</param>
        /// <param name="cmdParams">SQL参数.</param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, params DbParameter[] cmdParams)
        {
            return ExecuteScalar(CommandType.Text, sql, cmdParams);
        }
        /// <summary>
        /// 执行命令返回结果集中第一行第一列
        /// </summary>
        /// <param name="cmdType">命令类型.</param>
        /// <param name="cmdText">命令.</param>
        /// <param name="cmdParams">SQL参数.</param>
        /// <returns></returns>
        public object ExecuteScalar(CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        {
            try
            {
                OpenConnection();
                var cmd = CreateCommand(cmdType, cmdText, cmdParams);
                if (this.Transaction != null)
                    cmd.Transaction = this.Transaction;
                this.DataTable = null;
                return cmd.ExecuteScalar();
            }
            finally
            {
                Dispose();
            }
        }
        /// <summary>
        /// 执行SQL返回第一个对象，数据库无结果返回null.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">SQL语句.</param>
        /// <param name="cmdParams">SQL参数.</param>
        /// <returns></returns>
        public T ExecuteEntity<T>(string sql, params DbParameter[] cmdParams) where T : class
        {
            return ExecuteEntity<T>(CommandType.Text, sql, cmdParams);
        }
        /// <summary>
        /// 执行命令返回第一个对象，数据库无结果返回null.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmdType">命令类型.</param>
        /// <param name="cmdText">命令.</param>
        /// <param name="cmdParams">SQL参数.</param>
        /// <returns></returns>
        public T ExecuteEntity<T>(CommandType cmdType, string cmdText, params DbParameter[] cmdParams) where T : class
        {
            var entity = ExecuteList<T>(cmdType, cmdText, cmdParams).SingleOrDefault();
            if (entity == null)
            {
                var property = typeof(T).GetField("Empty", BindingFlags.Static | BindingFlags.Public);
                if (property != null)
                    entity = (T)property.GetValue(null);
            }
            return entity;
        }
        /// <summary>
        /// 执行SQL返回泛型集合.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">SQL语句.</param>
        /// <param name="cmdParams">SQL参数.</param>
        /// <returns></returns>
        public IList<T> ExecuteList<T>(string sql, params DbParameter[] cmdParams) where T : class
        {
            return ExecuteList<T>(CommandType.Text, sql, cmdParams);
        }
        /// <summary>
        /// 执行命令返回泛型集合.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmdType">命令类型.</param>
        /// <param name="cmdText">命令.</param>
        /// <param name="cmdParams">SQL参数.</param>
        /// <returns></returns>
        public IList<T> ExecuteList<T>(CommandType cmdType, string cmdText, params DbParameter[] cmdParams) where T : class
        {
            var dt = ExecuteDataSet(cmdType, cmdText, cmdParams).Tables[0];
            return ToEnties<T>(dt);
        }
        /// <summary>
        /// 在事务中执行方法体.
        /// </summary>
        /// <param name="act">The act.</param>
        public void ExecuteWithTransaction(Action act)
        {
            try
            {
                BeginTransaction();
                act();
                CommitTransaction();
            }
            catch (Exception ex)
            {
                //TODO:log exception
                RollbackTransaction();
                throw;
            }
        }

        private DbCommand CreateCommand(CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        {
            DbCommand cmd = _connection.CreateCommand();
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            cmd.Parameters.AddRange(cmdParams);
            if (_transaction != null)
                cmd.Transaction = _transaction;
            return cmd;
        }

        protected T ToEntity<T>(DataTable dataTable) where T : class
        {
            return ToEntity<T>(dataTable.AsEnumerable());
        }
        protected T ToEntity<T>(IEnumerable<DataRow> rows) where T : class
        {
            Type type = typeof(T);
            if (type.IsValueType || type == typeof(string))
                return CreateValue<T>(rows.First());
            else
                return CreateObject<T>(rows.First());
        }
        protected IList<T> ToEnties<T>(DataTable dataTable) where T : class
        {
            //TODO:性能有待考验
            return ToEnties<T>(dataTable.AsEnumerable());
        }
        protected IList<T> ToEnties<T>(IEnumerable<DataRow> rows) where T : class
        {
            Type type = typeof(T);
            if (type.IsValueType || type == typeof(string))
                return CreateValue<T>(rows);
            else
                return CreateObject<T>(rows);
        }
        private IList<T> CreateObject<T>(DataTable dataTable) where T : class
        {
            #region
            //Type type = typeof(T);
            //IList<T> list = new List<T>();
            //PropertyInfo[] properties = type.GetProperties(BindingFlags.Instance | BindingFlags.Public);
            //string name = string.Empty;
            //foreach (DataRow row in dataTable.Rows)
            //{
            //    T local = Activator.CreateInstance<T>();
            //    for (int i = 0; i < dataTable.Columns.Count; i++)
            //    {
            //        name = dataTable.Columns[i].ColumnName;
            //        var property = properties.Where(it => it.Name == name).SingleOrDefault();
            //        if (property != null)
            //        {
            //            object propertyValue;
            //            if (property.PropertyType.IsEnum)
            //                propertyValue = Enum.ToObject(property.PropertyType, row[property.Name]);
            //            else
            //                propertyValue = Convert.ChangeType(row[property.Name], property.PropertyType);
            //            property.SetValue(local, propertyValue, null);
            //        }
            //    }
            //    list.Add(local);
            //}
            //return list;
            #endregion
            return CreateObject<T>(dataTable.AsEnumerable());
        }
        private IList<T> CreateObject<T>(IEnumerable<DataRow> rows) where T : class
        {
            Type type = typeof(T);
            IList<T> list = new List<T>();
            PropertyInfo[] properties = type.GetProperties(BindingFlags.Instance | BindingFlags.Public);
            string name = string.Empty;
            foreach (DataRow row in rows)
            {
                T local = CreateObject<T>(row);
                list.Add(local);
            }
            return list;
        }
        private IList<T> CreateValue<T>(DataTable dataTable)
        {
            #region
            //Type type = typeof(T);
            //IList<T> list = new List<T>();
            //foreach (DataRow row in dataTable.Rows)
            //{
            //    T local = (T)Convert.ChangeType(row[0], type, null);
            //    list.Add(local);
            //}
            //return list;
            #endregion
            return CreateValue<T>(dataTable.AsEnumerable());
        }
        private IList<T> CreateValue<T>(IEnumerable<DataRow> rows)
        {
            Type type = typeof(T);
            IList<T> list = new List<T>();
            foreach (DataRow row in rows)
            {
                T local = (T)Convert.ChangeType(row[0], type, null);
                list.Add(local);
            }
            return list;
        }
        private T CreateObject<T>(DataRow row) where T : class
        {
            Type type = typeof(T);
            return CreateObject(row, type) as T;
        }
        private T CreateValue<T>(DataRow row)
        {
            Type type = typeof(T);
            T local = (T)Convert.ChangeType(row, type, null);
            return local;
        }
        private object CreateObject(DataRow row, Type targetType)
        {
            IEnumerable<PropertyInfo> properties = targetType.GetProperties(BindingFlags.Instance | BindingFlags.Public);
            var entitieProperties = properties.Where(it => it.PropertyType.GetInterface("IEntity") != null);
            properties = properties.Except(entitieProperties);      //去掉实体属性
            string name = string.Empty;
            object local;//= Activator.CreateInstance(targetType);
            local = FillObject(row, targetType, properties);
            foreach (var property in entitieProperties)
            {
                var proValue = CreateObject(row, property.PropertyType);
                property.SetValue(local, proValue, null);
            }
            return local;
        }
        private object FillObject(DataRow row, Type type, IEnumerable<PropertyInfo> properties)
        {
            string name = string.Empty;
            object obj = Activator.CreateInstance(type);
            for (int i = 0; i < row.Table.Columns.Count; i++)
            {
                name = row.Table.Columns[i].ColumnName;
                //联合查询出现相同字段的时候把该字段别名为  类型名称_属性名称，对应  表实体类名_字段名称
                var property = properties.Where(it => (string.Concat(it.DeclaringType.Name, "_", it.Name) == name) || (it.Name == name)).SingleOrDefault();
                if (property != null)
                {
                    object propertyValue;
                    if (property.PropertyType.IsEnum)
                        propertyValue = Enum.ToObject(property.PropertyType, row[name]);
                    else
                    {
                        Type t = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
                        propertyValue = row[name] == DBNull.Value ? null : Convert.ChangeType(row[name], t);
                    }
                    property.SetValue(obj, propertyValue, null);
                }
            }
            return obj;
        }

        #endregion

        #region IDisposable 成员

        public void Dispose()
        {
            if (_transaction == null)
            {
                if (_connection != null)
                {
                    _connection.Dispose();
                    _connection.Close();
                    _connection = null;
                }
            }
        }

        #endregion
    }
}
